USE ConsultTracking
go
drop proc CreateJob
drop proc UpdateJob
drop proc AssignUserToJob
drop proc GetJobDataWithinDates
drop proc GetAllJobDataByUserID
drop proc GetNonFinalizedJobs
drop proc GetJobInfoByJobID
drop proc GetPurchaseOrdersByTaskID
drop proc GetNotesByTaskID
GO
-------------------------------------------------
create proc CreateJob
@CustomerID int,
@Description nvarchar(100),
@BillingMethodID int,
@JobID int output,
@Created smalldatetime output,
@Modified smalldatetime output
as
INSERT INTO [ConsultTracking].[dbo].[Job]
           ([CustomerID]
           ,[Description]
           ,[BillingMethodID])
     VALUES
           (@CustomerID
           ,@Description
           ,@BillingMethodID)

SELECT @JobID = [JobID]
      ,@Created = [Created]
      ,@Modified = [Modified]
  FROM [ConsultTracking].[dbo].[Job]
where JobID = SCOPE_IDENTITY()
------------------------------------------------
go
create proc AssignUserToJob
@JobID int,
@UserID int,
@UserJobID int output,
@Created smalldatetime output,
@Modified smalldatetime output

as
INSERT INTO [ConsultTracking].[dbo].[UserJob]
           ([JobID]
           ,[UserID])
     VALUES
           (@JobID
           ,@UserID)

select @UserJobID = UserJobID
	,@Modified = Modified
	,@Created = Created
from UserJob
where UserJobID = Scope_Identity()

-------------------------------------------------
go
create proc UpdateJob
@JobID int,
@Description nvarchar(100),
@BillingMethod int,
@Finalized bit,
@Modified smalldatetime
as
UPDATE [ConsultTracking].[dbo].[Job]
   SET [Description] = @Description
      ,[BillingMethodID] = @BillingMethod
      ,[Finalized] = @Finalized
	  ,[Modified] = GETDATE()
 WHERE JobID = @JobID and Modified = @Modified

select @Modified = Modified
from Job
where JobID = @JobID
-----------------------------------------------
go
create proc GetJobDataWithinDates
@StartDate smalldatetime,
@EndDate smalldatetime
as
select * from Job 
where Created > @StartDate
and Created < @EndDate
-------------------------------------------------
go 
create proc GetAllJobDataByUserID
@UserID int
as

SELECT j.[JobID]
      ,j.[CustomerID]
      ,j.[Description]
      ,j.[BillingMethodID]
      ,j.[Finalized]
      ,j.[Created]
      ,j.[Modified]
  FROM [ConsultTracking].[dbo].[Job]j
join UserJob uj
on j.JobID = uj.JobID
where uj.UserID = @UserID;

SELECT distinct t.[TaskID]
      ,t.[JobID]
      ,t.[LocationID]
      ,t.[Description]
      ,t.[StartTime]
      ,t.[EndTime]
      ,t.[Addressed]
      ,t.[Status]
      ,t.[Created]
      ,t.[Modified]
  FROM [ConsultTracking].[dbo].[Task] t
join Job j on j.JobID = t.JobID
join UserJob uj on uj.JobID = j.JobID
where uj.UserID = @UserID

SELECT n.[NoteID]
      ,n.[TaskID]
      ,n.[UserID]
      ,n.[Description]
      ,n.[Created]
      ,n.[Modified]
  FROM [ConsultTracking].[dbo].[Note] n
join [User] u
on n.UserID = u.UserID
where n.UserID = @UserID

SELECT  distinct p.[PurchaseOrderID] 
      ,p.[TaskID]
      ,p.[Description]
      ,p.[Manufacturer]
      ,p.[ModelNumber]
      ,p.[Quantity]
      ,p.[Price]
      ,p.[Created]
      ,p.[Modified]
  FROM [ConsultTracking].[dbo].[PurchaseOrder] p
join Task t on p.TaskID = t.TaskID
join Job j on j.JobID = t.JobID
join UserJob uj on uj.JobID = j.JobID
where uj.UserID = @UserID
order by created

------------------------------------------------------------------
go
create proc GetNonFinalizedJobs
as
select distinct j.JobID, j.CustomerID, cu.CustomerName as [Customer Name], j.Description,
	j.DueDate as [Due Date], bm.Description
from Job j
join Customer cu
on
j.CustomerID = cu.CustomerID
join BillingMethod bm
on j.BillingMethodID = bm.BillingMethodID
-------------------------------------------------------------------
go
create proc GetJobInfoByJobID
@JobID int
as
select distinct j.JobID, j.CustomerID, cu.CustomerName, j.Description, j.[BillingMethodID] ,bm.Description, j.[Finalized], j.DueDate,
	j.Created, j.Modified 
from Job j
join Customer cu on j.CustomerID = cu.CustomerID
join BillingMethod bm on j.[BillingMethodID] = bm.[BillingMethodID]
where j.JobID = @JobID
	
select distinct u.UserID, u.LogOnName as Consultant
from [User] u
join UserJob uj on uj.UserID = u.UserID
where uj.JobID = @JobID

select distinct t.TaskID, t.Description, t.LocationID, t.StartTime, t.EndTime, t.Addressed, t.Status, t.Created, t.Modified
from Task t
join Job j on t.JobID = j.JobID
join Location l on t.LocationID = l.LocationID
where t.JobID = @JobID

--------------------------------------------------------------------
go
create proc GetPurchaseOrdersByTaskID
@TaskID int
as
select distinct po.PurchaseOrderID, u.LogOnName, po.Description, po.Manufacturer, po.ModelNumber, po.Quantity, po.Price, po.Created,
	po.Modified
from PurchaseOrder po
join [User] u on po.[UserID] = u.[UserID]
where po.TaskID = @TaskID;
--------------------------------------------------------------------
go
create proc GetNotesByTaskID
@TaskID int
as
select n.NoteID, u.LogOnName, n.Description, n.Created, n.Modified
from Note n
join [User] u on n.UserID = u.UserID
join [Task] t on n.TaskID = t.TaskID
where n.taskID = @TaskID

-------------------------------------------------------------------
